# compute the following event's happening probability for each user
# 1. voice call
# 2. meet fixed-location Bluetooth device

import pymysql as db
import sqlite3
from datetime import datetime
from datetime import timedelta

#conn = db.connect(host='127.0.0.1', port=3306, user='root', passwd='wlywly', db='mit_reality_full')
conn = sqlite3.connect('E:/dataset research/nodobo-release/db/nodobo.db')

#query common function
def execute_query_one_value(query):
	cur = conn.cursor()
	cur.execute(query) 
	result = cur.fetchone()
	value = None
	if result is not None:
	   value = result[0] 
	cur.close()
	return value

def execute_query_fetch_all(query):
    cur = conn.cursor()
    cur.execute(query) 
    results = cur.fetchall() 
    cur.close()
    return results

#training data set: 2004-10-4 ~ 2004-11-14, total 6 weeks
START_DATE = datetime(2010, 10, 4, 0, 0, 0)
WEEK_LENGTH = 6

#related to the granularity of the generated probability
#unit: minutes
#as user's action is hard to be precise to minute, 30 or 60 minutes may be appropriate
TIME_SPLIT = 60

#DATA_USERS_ID = [29,39,79,37,47,63,91,15,57,80,75,69,94,78,43,95,72,73,16,85,88,60,35,54,50,66,17,18,20,28]
#DATA_USER_DEVICES_ID = [15,16,17,49,18,40,20,29,30,36,38,41,93,46,51,54,58,61,64,67,70,73,76,77,79,83,84,85,90,94,97,100,101]
#NON_DATA_USERS_ID = [1,2,3,4,6,7,8,10,11,12,14,19,22,23,24,26,32,33,36,38,40,42,46,49,51,59,61,65,70,74,76,77,81,83,84,86,89,90,92,96,97]
#FIXED_DEVICES_ID = [120, 438]

#nodobo settings
DATA_USERS_ID = [1,2,3,4,5,6,7,8,9,10,11,12,13]
NON_DATA_USERS_ID = [14,15,16,17,18,19,20,21,22,23,24,25,26,27]

def has_call_nodobo(user_id, date_query, time_slot):
	starttime = date_query + timedelta(minutes=time_slot*TIME_SPLIT)
	endtime = date_query + timedelta(days=1)
	query = "select count(*) from calls where user_id = {uid} and call_timestamp between '{start}' and '{end}'".format(uid=user_id, start=starttime, end=endtime)
	val = execute_query_one_value(query)
	if val > 0:
		return True
	else:
		return False

def has_call(user_id, date_query, time_slot):
	starttime = date_query + timedelta(minutes=time_slot*TIME_SPLIT)
	endtime = date_query + timedelta(days=1)
	query = "select count(*) from callspan where person_oid={uid} and starttime between '{start}' and '{end}' and description='Voice Call'".format(uid=user_id, start=starttime, end=endtime)
	#print query
	val = execute_query_one_value(query)
	if val > 0:
		return True
	else:
		return False

def meet_device(user_id, date_query, time_slot):
	starttime = date_query + timedelta(minutes=time_slot*TIME_SPLIT)
	endtime = date_query + timedelta(days=1)
	device_ids = ",".join(str(x) for x in FIXED_DEVICES_ID)
	query = "select count(*) from devicespan where person_oid = {uid} and starttime between '{start}' and '{end}' and device_oid in ({devices})".format(uid=user_id, start=starttime, end=endtime, devices=device_ids)
	val = execute_query_one_value(query)
	if val > 0:
		return True
	else:
		return False

class CriticalEvent:
	def __init__(self, user_id, event_time, event_type):
		self.user = user_id
		self.time = event_time
		self.type = event_type #0:voice call, 1:meet device, 2:meet other user
		self.user2 = "None"

	def description(self):
		return "{}\t{}\t{}\t{}".format(self.time, self.type, self.user, self.user2)



def meet_data_user_nodobo(user_id, date_query, time_slot, meet_data_user_events):
	starttime = date_query + timedelta(minutes=time_slot*TIME_SPLIT)
	endtime = date_query + timedelta(days=1)
	for e in meet_data_user_events:
		if e.user == user_id and starttime <= e.time and endtime >=e.time:
			return True
	return False


def find_all_non_data_meet_data_events(starttime, endtime):
	non_data_users = ",".join(str(x) for x in NON_DATA_USERS_ID)
	data_users = ",".join(str(x) for x in DATA_USERS_ID)
	query = "select user_id, timestamp from presences where user_id in ({nd_id}) and timestamp between '{start}' and '{end}' and other_id in ({data_user_ids})".format(nd_id=non_data_users, start=starttime, end=endtime, data_user_ids=data_users)
	val = execute_query_one_value(query)
	results = execute_query_fetch_all(query)
	events = []
	for result in results:
		new_meet_event = CriticalEvent(result[0], datetime.strptime(result[1],'%Y-%m-%d %H:%M:%S'), 2)
		events.append(new_meet_event)
	return events

def meet_data_user_or_device(user_id, date_query, time_slot):
	starttime = date_query + timedelta(minutes=time_slot*TIME_SPLIT)
	endtime = date_query + timedelta(days=1)
	device_ids = ",".join(str(x) for x in FIXED_DEVICES_ID+DATA_USER_DEVICES_ID)
	query = "select count(*) from devicespan where person_oid = {uid} and starttime between '{start}' and '{end}' and device_oid in ({devices})".format(uid=user_id, start=starttime, end=endtime, devices=device_ids)
	val = execute_query_one_value(query)
	if val > 0:
		return True
	else:
		return False

def receive_data_from_other_user(user_id, date_query, time_slot):
	starttime = date_query + timedelta(minutes=time_slot*TIME_SPLIT)
	endtime = date_query + timedelta(days=1)
	query = "select count(*) from new_bt_user_transfer_event where user_receive = {uid} and time between '{start}' and '{end}'".format(
		uid=user_id, start=starttime, end=endtime)
	val = execute_query_one_value(query)
	if val > 0:
		return True
	else:
		return False

def calculate_data_user_probability_nodobo():
	output = ""
	for u in DATA_USERS_ID:
		print u
		for i in range(0, 7):
			for time_slot in range(0, 24*60/TIME_SPLIT):
				#calculate two events
				num_voice_call = 0.0
				num_meet_device = 0.0 #always zero compitable to MIT Reality experiment
				for k in range(0, WEEK_LENGTH):
					date_query = START_DATE + timedelta(days=i+k*7)
					if (has_call_nodobo(u, date_query, time_slot)):
						num_voice_call += 1
				p_voice_call = num_voice_call / WEEK_LENGTH
				p_meet_device = num_meet_device / WEEK_LENGTH
				output += "{}\t{}\t{}\t{}\t{}\n".format(u, i, time_slot, p_voice_call, p_meet_device)
	with open('event_probability_nodobo.tsv','w') as outputfile:
		outputfile.write(output)

def calculate_data_user_probability():
	output = ""
	for u in DATA_USERS_ID:
		print u
		for i in range(0, 7):
			for time_slot in range(0, 24*60/TIME_SPLIT):
				#calculate two events
				num_voice_call = 0.0
				num_meet_device = 0.0
				for k in range(0, WEEK_LENGTH):
					date_query = START_DATE + timedelta(days=i+k*7)
					if (has_call(u, date_query, time_slot)):
						num_voice_call += 1
					if (meet_device(u, date_query, time_slot)):
						num_meet_device += 1
				p_voice_call = num_voice_call / WEEK_LENGTH
				p_meet_device = num_meet_device / WEEK_LENGTH
				output += "{}\t{}\t{}\t{}\t{}\n".format(u, i, time_slot, p_voice_call, p_meet_device)
	with open('event_probability.tsv','w') as outputfile:
		outputfile.write(output)

def calculate_non_data_user_probability_nodobo():
	output = ""
	starttime = START_DATE
	endtime = START_DATE + timedelta(days=7*WEEK_LENGTH)
	all_meet_events = find_all_non_data_meet_data_events(starttime, endtime)
	print 'ok'
	for u in NON_DATA_USERS_ID:
		print u
		for i in range(0, 7):
			for time_slot in range(0, 24*60/TIME_SPLIT):
				#calculate one events
				num_meet = 0.0
				for k in range(0, WEEK_LENGTH):
					date_query = START_DATE + timedelta(days=i+k*7)
					if (meet_data_user_nodobo(u, date_query, time_slot, all_meet_events)):
						num_meet += 1
				p_meet = num_meet / WEEK_LENGTH
				output += "{}\t{}\t{}\t{}\n".format(u, i, time_slot, p_meet)
	with open('non_data_event_probability_nodobo.tsv','w') as outputfile:
		outputfile.write(output)

def calculate_non_data_user_probability():
	output = ""
	for u in NON_DATA_USERS_ID:
		print u
		for i in range(0, 7):
			for time_slot in range(0, 24*60/TIME_SPLIT):
				#calculate one events
				num_meet = 0.0
				for k in range(0, WEEK_LENGTH):
					date_query = START_DATE + timedelta(days=i+k*7)
					if (meet_data_user_or_device(u, date_query, time_slot)):
						num_meet += 1
				p_meet = num_meet / WEEK_LENGTH
				output += "{}\t{}\t{}\t{}\n".format(u, i, time_slot, p_meet)
	with open('non_data_event_probability.tsv','w') as outputfile:
		outputfile.write(output)

def calculate_data_user_probability_receive_other_results_nodobo():
	output = ""
	for u in DATA_USERS_ID:
		print u
		for i in range(0, 7):
			for time_slot in range(0, 24*60/TIME_SPLIT):
				num_receive_from_other = 0.0
				for k in range(0, WEEK_LENGTH):
					date_query = START_DATE + timedelta(days=i+k*7)
					if (receive_data_from_other_user(u, date_query, time_slot)):
						num_receive_from_other += 1
				p_receive_from_other = num_receive_from_other / WEEK_LENGTH
				output += "{} {} {} {}\n".format(u, i, time_slot, p_receive_from_other)
	with open('data_user_receive_other_probability_nodobo.tsv','w') as outputfile:
		outputfile.write(output)

def calculate_data_user_probability_receive_other_results():
	output = ""
	for u in DATA_USERS_ID:
		print u
		for i in range(0, 7):
			for time_slot in range(0, 24*60/TIME_SPLIT):
				num_receive_from_other = 0.0
				for k in range(0, WEEK_LENGTH):
					date_query = START_DATE + timedelta(days=i+k*7)
					if (receive_data_from_other_user(u, date_query, time_slot)):
						num_receive_from_other += 1
				p_receive_from_other = num_receive_from_other / WEEK_LENGTH
				output += "{} {} {} {}\n".format(u, i, time_slot, p_receive_from_other)
	with open('data_user_receive_other_probability.tsv','w') as outputfile:
		outputfile.write(output)


if __name__ == '__main__':
	#calculate_data_user_probability()
	#calculate_non_data_user_probability()
	#calculate_data_user_probability_receive_other_results()
	calculate_data_user_probability_nodobo()
	calculate_non_data_user_probability_nodobo()
	calculate_data_user_probability_receive_other_results_nodobo()
	conn.close()